﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAL;
using BLL;
using System.Data.SqlClient;
using Xuatfile;
using System.IO;
//using iTextSharp.text;
//using iTextSharp.text.pdf;

//using iTextSharp.text.html.simpleparser;

namespace _3tiers
{
    public partial class tab1_Form_XemLichDay : Form
    {
        public tab1_Form_XemLichDay()
        {
            InitializeComponent();
            string sql3 = "select khoa.TenKhoa as Khoa,gv.TenGiaoVien as Ten_Giao_Vien,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblChiTietLich chitiet,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and chitiet.IDChiTietLich=ld.IDChiTietLich and m.IDMon=chitiet.IDMon and khoa.IDKhoa=gv.IDKhoa and chitiet.IDNhom=nh.IDNhom ";
            dataGridView1.DataSource = business.ShowData(sql3);
        }

        DataAccess ac = new DataAccess();
        BLL.BLL business = new BLL.BLL();

        //string idKhoa;
        private void tab1_Form_XemLichDay_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'khoaDataSet.tblKhoa' table. You can move, or remove it, as needed.
            this.tblKhoaTableAdapter.Fill(this.khoaDataSet.tblKhoa);
        }
        //Hiển thị mã giáo viên ứng với từng khoa.
        private void cbbKhoa_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                string idKhoa = cbbKhoa.SelectedValue.ToString();
                string sql = "select * from tblGiaoVien where IDKhoa='" + idKhoa + "'";
                DataTable dt = ac.GetTable(sql);
                cbbMaGV.DataSource = dt;
                cbbMaGV.DisplayMember = "IDGiaoVien";
                cbbMaGV.ValueMember = "IDGiaoVien";
            }
            catch { };            
        }
        //string idGiaoVien;
        void fillCombobox()
        {
            try
            {
                string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                tbTenGiaoVien.Text = gv;

            }
            catch { }
        }
        //Truy vấn tên giáo viên theo mã giáo viên ứng với từng khoa.
        private void cbbMaGV_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //tbTenGiaoVien.Text = "";
                string idGiaoVien=cbbMaGV.SelectedValue.ToString();            
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                tbTenGiaoVien.Text = gv;    
            }
            catch { }
            btLichDay_Click(sender, e);
        }
        //truy vấn lịch dạy ứng với từng mã giáo viên.
        private void btLichDay_Click(object sender, EventArgs e)
        {
            string idGiaoVien = cbbMaGV.SelectedValue.ToString();
            string sql3 = "select gv.TenGiaoVien as Ten_Giao_Vien,khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblChiTietLich chitiet,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and chitiet.IDChiTietLich=ld.IDChiTietLich and m.IDMon=chitiet.IDMon and khoa.IDKhoa=gv.IDKhoa and chitiet.IDNhom=nh.IDNhom and gv.IDGiaoVien='" + idGiaoVien + "'";
            dataGridView1.DataSource = business.ShowData(sql3);
            tab1_Form_XemLichDay_Load(sender, e);
            hienThiThongTinGiaoVien();
        }

        void hienThiThongTinGiaoVien()
        {
            //Hiển thị thông tin giáo viên.
            try
            {
                //tbTenGiaoVien.Text = "";
                string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                lbThongTinGV.Text = "Giáo viên:"+gv;

                string sql5 = "select TenKhoa from tblGiaoVien gv,tblKhoa khoa where gv.IDKhoa=khoa.IDKhoa and IDGiaoVien='" + idGiaoVien + "'";
                string khoa = ac.ExcuteScalar(sql5);
                lbThongTinGV.Text += " \n Khoa: " +khoa;

                string sql3 = "select HocVi from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string hv = ac.ExcuteScalar(sql3);
                lbThongTinGV.Text += "\n Học vị:" + hv;

                string sql4 = "select bm.TenBoMon from tblGiaoVien gv,tblBoMon bm where gv.IDBoMon=bm.IDBoMon and IDGiaoVien='" + idGiaoVien + "'";
                string bm=ac.ExcuteScalar(sql4);
                lbThongTinGV.Text += " \n Bộ môn: " + bm;                
            }
            catch { lbThongTinGV.Text = "Lỗi truy cập. Vui lòng chọn mã giáo viên  " ; }               
            
        }

        private void btXuatFile_Click(object sender, EventArgs e)
        {
            //ExportToWord(sender, e);
        }
        string file = "D:\\";
        private void btXuat_Click(object sender, EventArgs e)
        {
            string idGiaoVien = cbbMaGV.SelectedValue.ToString();
            string sql3 = "select gv.TenGiaoVien as Ten_Giao_Vien,khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblChiTietLich chitiet,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and chitiet.IDChiTietLich=ld.IDChiTietLich and m.IDMon=chitiet.IDMon and khoa.IDKhoa=gv.IDKhoa and chitiet.IDNhom=nh.IDNhom and gv.IDGiaoVien='" + idGiaoVien + "'";
            DataTable dt=ac.GetTable(sql3);
            XuatRaExcel.Xuat(file, "Lịch dạy", dt);
        }
        
    }
}
